/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package talvan;

import java.util.ArrayList;
import java.util.Date;

/**
 * Klassi sum hevur til uppgávu at senda sql query víðari til Dátagrunnin
 * hann hevur 2 fields
 * DbHandler fyri at kunna semda boð í millum forriti og dátagrunnin
 * @author DJH
 */
public class SQLCallerNewEvent {
    
    private DbHandler dbh;
    private int userID;
    
    public SQLCallerNewEvent(DbHandler dbh, int userID)
    {
        this.dbh = dbh;
        this.userID = userID;
    }
    
    /**
     * metoda sum heintar øll Employe´s 
     * @return list við øllum employes
     */
    public ArrayList<Employe> getEmployes()
    {
        ArrayList<Employe> resultList = new ArrayList<Employe>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Employe.firstName as name, Employe.id as id, Devision.name as devName, Devision.id as devision "
                     + "FROM Employe JOIN Devision "
                     + "ON Devision.id = Employe.devision_id ";
                
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //loop sum creatar objects og addar í array ella Arraylist?
                while (rs.next()) {
                    Employe newEmploye = new Employe();
                    
                    newEmploye.setFirstName(rs.getString("name"));
                    String idString = rs.getString("id");
                    newEmploye.setID(Integer.parseInt(idString));
                    newEmploye.setDevisionID(Integer.parseInt(rs.getString("devision")));
                    newEmploye.setDevisionName(rs.getString("devName"));
                    resultList.add(newEmploye);
                    
                }
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        return resultList;
    }
    
    /**
     * Metoda sum heintar employes sum eru úr deildini við devisionID
     * @param devision ID
     * @return employes við devision id
     */
    public ArrayList<Employe> getEmployes(int devisionID)
    {
        ArrayList<Employe> resultList = new ArrayList<Employe>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Employe.firstName as name, Employe.id as id, Devision.name as devName, Devision.id as devision "
                     + "FROM Employe JOIN Devision "
                     + "ON Devision.id = Employe.devision_id "
                     + "WHERE Devision.id = " + devisionID + " ; ";
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //loop sum creatar objects og addar í array ella Arraylist?
                while (rs.next()) {
                    Employe newEmploye = new Employe();
                    
                    newEmploye.setFirstName(rs.getString("name"));
                    String idString = rs.getString("id");
                    newEmploye.setID(Integer.parseInt(idString));
                    newEmploye.setDevisionID(Integer.parseInt(rs.getString("devision")));
                    newEmploye.setDevisionName(rs.getString("devName"));
                    resultList.add(newEmploye);
                }
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
       
        return resultList;
    }
    
    /**
     * heintar øll devisions
     * @return lista við devisions
     */
    public ArrayList<Devision> getDevisions()
    {
        ArrayList<Devision> resultList = new ArrayList<Devision>();
        java.sql.ResultSet rs = null;
        try {
            if (dbh.getConnection() != null) {
                String sql = "USE TALVAN; ";
                sql += "SELECT Devision.name as name, Devision.id as id "
                     + "FROM Devision ";
                
                java.sql.Statement stmt = dbh.getConnection().createStatement();
                rs = stmt.executeQuery(sql);
                
                //loop sum creatar objects og addar í array ella Arraylist?
                while (rs.next()) {
                    Devision d = new Devision();
                    d.setName(rs.getString("name"));
                    String id = rs.getString("id");
                    d.setId(Integer.parseInt(id));
                    System.out.println("devision = " + d.getId());
                    resultList.add(d);
                }
                rs.close();
                rs = null;
                dbh.closeConnection();
            } else {
                throw new Exception("Error: No active Connection");
            }
        } catch (Exception e) {
            //throw new Exception("SQL statement í DataHandler (getMeetings metodan)");
        }
        return resultList;
    }

    /**
     * Metoda sum tekur ein string og convertar hann til eitt Date type
     * @param stringDate
     * @return Date
     */
    private Date convertToDate(String stringDate)
    {
        int year = Integer.parseInt(stringDate.substring(0, 4));
        int month = Integer.parseInt(stringDate.substring(5, 7));
        int day = Integer.parseInt(stringDate.substring(8, 10));
        int hour = Integer.parseInt(stringDate.substring(11, 13));
        int min = Integer.parseInt(stringDate.substring(14, 16));
                    
        Date date = new Date(year, month, day, hour, min);
        return date;
    }
    
}
